--=============== МОДУЛЬ 5. РАБОТА С POSTGRESQL =======================================
--= ПОМНИТЕ, ЧТО НЕОБХОДИМО УСТАНОВИТЬ ВЕРНОЕ СОЕДИНЕНИЕ И ВЫБРАТЬ СХЕМУ PUBLIC===========
SET search_path TO public;

--======== ОСНОВНАЯ ЧАСТЬ ==============

--ЗАДАНИЕ №1
--Сделайте запрос к таблице payment и с помощью оконных функций добавьте вычисляемые 
--колонки согласно условиям:
--Пронумеруйте все платежи от 1 до N по дате
--Пронумеруйте платежи для каждого покупателя, сортировка платежей должна быть по дате
--Посчитайте нарастающим итогом сумму всех платежей для каждого покупателя, сортировка должна 
--быть сперва по дате платежа, а затем по сумме платежа от наименьшей к большей
--Пронумеруйте платежи для каждого покупателя по стоимости платежа от наибольших к меньшим 
--так, чтобы платежи с одинаковым значением имели одинаковое значение номера.
--Можно составить на каждый пункт отдельный SQL-запрос, а можно объединить все колонки 
--в одном запросе.

select customer_id, payment_id, payment_date, 
	ROW_NUMBER() OVER (ORDER BY payment_date) as Column_1,
	ROW_NUMBER() OVER (PARTITION BY customer_id
		ORDER BY payment_date) as Column_2,		
    SUM(amount) OVER (PARTITION BY customer_id 
    	ORDER BY payment_date,amount) as Column_3,
	dense_rank() over (PARTITION BY customer_id ORDER BY amount desc ) as Column_4
from payment p
order by customer_id, Column_4


--ЗАДАНИЕ №2
--С помощью оконной функции выведите для каждого покупателя стоимость платежа и стоимость 
--платежа из предыдущей строки со значением по умолчанию 0.0 с сортировкой по дате.

-- В ответе Ориентировался на скриншот. 
-- Если оствить сортировку только по дате - значения иные будут.
select customer_id, payment_id, payment_date, 
	amount,
	LAG(amount, 1, 0.0) OVER () AS last_amount
from payment p
order by customer_id, payment_id, payment_date



--ЗАДАНИЕ №3
--С помощью оконной функции определите, на сколько каждый следующий платеж покупателя больше или меньше текущего.

select customer_id, payment_id, payment_date, 
	amount,
	amount - LEAD(amount, 1, 0.0) OVER () AS last_amount
from payment p
order by customer_id, payment_id, payment_date


--ЗАДАНИЕ №4
--С помощью оконной функции для каждого покупателя выведите данные о его последней оплате аренды.

select customer_id, payment_id, payment_date, amount
from payment
where payment_date in (SELECT MAX(payment_date) OVER (PARTITION BY customer_id ORDER BY payment_date desc ) FROM payment)
order by customer_id  limit 10


--======== ДОПОЛНИТЕЛЬНАЯ ЧАСТЬ ==============

--ЗАДАНИЕ №1
--С помощью оконной функции выведите для каждого сотрудника сумму продаж за август 2005 года 
--с нарастающим итогом по каждому сотруднику и по каждой дате продажи (без учёта времени) 
--с сортировкой по дате.

select staff_id, 
	payment_date,
	sum_amount,
	coalesce(SUM(sum_amount) OVER (PARTITION BY staff_id 
    	ORDER BY payment_date::DATE),0) as "sum"
from (select staff_id , 
	to_char(payment_date::DATE,'dd.mm.yyyy') AS "payment_date",
	SUM(amount) as sum_amount
from payment p 
group by staff_id, payment_date::DATE
having payment_date::DATE BETWEEN '01-08-2005 00:00:00' AND '31-08-2005 23:59:59' ) as tests
order by staff_id, payment_date


--ЗАДАНИЕ №2
--20 августа 2005 года в магазинах проходила акция: покупатель каждого сотого платежа получал
--дополнительную скидку на следующую аренду. С помощью оконной функции выведите всех покупателей,
--которые в день проведения акции получили скидку

select customer_id, payment_date, payment_number  
from (select
	customer_id,
	payment_date,
	ROW_NUMBER() OVER (ORDER BY payment_date) as payment_number
	from payment p 
	where payment_date::DATE = '2005-08-20'
	) as zapros
where to_char(payment_number, '000') LIKE '%00'
order by payment_number


--ЗАДАНИЕ №3
--Для каждой страны определите и выведите одним SQL-запросом покупателей,
-- которые попадают под условия:
-- 1. покупатель, арендовавший наибольшее количество фильмов
-- 2. покупатель, арендовавший фильмов на самую большую сумму
-- 3. покупатель, который последним арендовал фильм



-- Попытки в решение

select * from customer c 

select * from payment p 

select customer_id, count(payment_id),  sum(amount),   MAX(payment_date)  
from payment p
GROUP BY customer_id
order by customer_id desc limit 5

select * from country c 

select concat(first_name,' ',last_name) as FIO, c3.country  
from customer c 
join address a on c.address_id = a.address_id 
join city c2 on a.city_id  = c2.city_id 
join country c3 on c2.country_id = c3.country_id 
join payment p on c.customer_id = p.customer_id 
group by c3.country, concat(first_name,' ',last_name)
order by c3.country 


-- Основной работаюющий код
select concat(c3.first_name,' ', c3.last_name) as FIO , 
	c1.country, 
	count(p.customer_id),
	sum(p.amount),
	max(p.payment_date)
from country c1 
join city c2 on c1.country_id  = c2.country_id  
join address a on c2.city_id  = a.city_id  
join customer c3 on a.address_id  = c3.address_id  
join payment p on c3.customer_id = p.customer_id 
group by c1.country, FIO
order by c1.country 

-- Тесты (на работающий или пробные варианты решения задачи, которые было жалко удалять)
select c1.country, 
concat(c3.first_name,' ', c3.last_name) as FIO,
fio2
from (select concat(first_name,' ',last_name) as FIO2  
	from customer c 
		join address a on c.address_id = a.address_id 
		join city c2 on a.city_id  = c2.city_id 
		join country c3 on c2.country_id = c3.country_id 
		join payment p on c.customer_id = p.customer_id 
		group by c3.country, concat(first_name,' ',last_name)
		having count(p.customer_id) in (select count(p.customer_id)
		from country c1 
		join city c2 on c1.country_id  = c2.country_id  
		join address a on c2.city_id  = a.city_id  
		join customer c3 on a.address_id  = c3.address_id  
		join payment p on c3.customer_id = p.customer_id 
		group by c1.country)) ted1,
		country c1 
join city c2 on c1.country_id  = c2.country_id  
join address a on c2.city_id  = a.city_id  
join customer c3 on a.address_id  = c3.address_id  
join payment p on c3.customer_id = p.customer_id 
group by c1.country, FIO, ted1.fio2


select concat(c.first_name,' ',c.last_name) as FIO, c3.country  
from customer c 
join address a on c.address_id = a.address_id 
join city c2 on a.city_id  = c2.city_id 
join country c3 on c2.country_id = c3.country_id 
join payment p on c.customer_id = p.customer_id 
group by c3.country, concat(c.first_name,' ',c.last_name)
having count(DISTINCT p.customer_id) in (select count(DISTINCT p.customer_id)
from country c1 
join city c2 on c1.country_id  = c2.country_id  
join address a on c2.city_id  = a.city_id  
join customer c3 on a.address_id  = c3.address_id  
join payment p on c3.customer_id = p.customer_id 
group by c1.country)
order by c3.country  

select count(DISTINCT p.customer_id) as FIO, c3.country  
from customer c 
join address a on c.address_id = a.address_id 
join city c2 on a.city_id  = c2.city_id 
join country c3 on c2.country_id = c3.country_id 
join payment p on c.customer_id = p.customer_id 
group by c3.country, concat(c.first_name,' ',c.last_name)
having  in (select count(DISTINCT p.customer_id)
from country c1 
join city c2 on c1.country_id  = c2.country_id  
join address a on c2.city_id  = a.city_id  
join customer c3 on a.address_id  = c3.address_id  
join payment p on c3.customer_id = p.customer_id 
group by c1.country)
order by c3.country  

select  concat(c3.first_name,' ', c3.last_name) as FIO,
	c1.country, 
	count(p.customer_id),
	sum(p.amount),
	max(p.payment_date)
from country c1 
join city c2 on c1.country_id  = c2.country_id  
join address a on c2.city_id  = a.city_id  
join customer c3 on a.address_id  = c3.address_id  
join payment p on c3.customer_id = p.customer_id 
group by c1.country
order by c1.country 
